package com.ruoyi.gbase.util;

import com.ruoyi.web.controller.common.CommonController;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

import java.sql.*;
import java.util.*;

@Component
public class GbaseUtil {

    private static final Logger log = LoggerFactory.getLogger(CommonController.class);

    //三大核心接口
    private static Connection conn = null;
    private static PreparedStatement pstmt = null;
    private static ResultSet rs = null;
    private static CallableStatement cs = null;

    @Value("${gbase.url}")
    private  String url;
    @Value("${gbase.className}")
    private  String className;
    @Value("${gbase.username}")
    private  String username;
    @Value("${gbase.password}")
    private  String password;

    public  Connection connectGBase() {

        //加载MySql的驱动类
        try {
            Class.forName(className);
        } catch (ClassNotFoundException e) {
            log.error("找不到驱动程序类 ，加载驱动失败！");
            e.printStackTrace();
        }
     /*   //URL
        String url = "jdbc:gbase://32.14.47.19:5258/gbase";
        //账号
        String username = "gbase";
        //密码
        String password = "gbase20110531";*/
        try {
            if(conn==null||conn.isClosed()){
                conn = DriverManager.getConnection(url,username,password);
            }
        } catch (SQLException e) {
            log.error("数据库连接失败！");
            e.printStackTrace();
        }
        return conn;
    }
    //关闭数据库连接
    public   void closeConnection(){
        if(rs!=null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(pstmt!=null){
            try {
                pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        if(conn!=null){
            try {
                conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * @Author 谷天乐
     * @Description 将ResultSet转换成List
     * @Date 2019/2/13 10:31
     * @Param [rs]
     * @return java.util.List
     **/
    public  List resultSetToList(ResultSet rs, String columnName) throws java.sql.SQLException {
        if(rs == null) {
            return Collections.EMPTY_LIST;
        }
        ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount(); //返回此 ResultSet 对象中的列数
        List list = new ArrayList();
        Map rowData;
        while (rs.next()) {
            rowData = new HashMap(columnCount);
            for (int i = 1; i <= columnCount; i++) {
                rowData.put(md.getColumnName(i), rs.getObject(i));
            }
            if(columnName==null) {
                list.add(rowData);
            }else {
                list.add(rowData.get(columnName));
            }
        }
        return list;
    }

    /**
     * @Author 谷天乐
     * @Description 查询方法
     * @Date 2019/2/13 10:32
     * @Param [sql]
     * @return java.sql.ResultSet
     **/
    public  ResultSet query(String sql) throws SQLException, ClassNotFoundException {
        //conn = connectGBase();
        pstmt = conn.prepareStatement(sql);
        rs = pstmt.executeQuery();
        return rs;
    }

    /**
     * @Author 谷天乐
     * @Description 支持insert,delete,update操作
     * 参数按顺序和占位符对应传入
     * demo:
     *  update("update aa set name = ? where id = ?",new Object[]{"7","1"});
    update("insert into aa (id,name) values (?,?)",new Object[]{"5","6"});
    update("delete from aa where id = ?",new Object[]{"5"});
     * @Date 2019/2/13 10:32
     * @Param [sql, values]
     * @return void
     **/
    public  int update(String sql,Object []values) throws SQLException, ClassNotFoundException {
        int result = 0;
        //获取数据库链接
        //conn=connectGBase();
            //预编译
            pstmt=conn.prepareStatement(sql);
            //获取ParameterMetaData()对象
            ParameterMetaData pmd=pstmt.getParameterMetaData();
            //获取参数个数
            int number=pmd.getParameterCount();
            //循环设置参数值
            for (int i = 1; i <=number; i++) {
                pstmt.setObject(i, values[i-1]);
            }
            result = pstmt.executeUpdate();
            return result;

    }

    /**
     * @Author 谷天乐
     * @Description 输出查询结果
     * @Date 2019/2/13 10:33
     * @Param [rs]
     * @return void
     **/
    public  void output(ResultSet rs) throws SQLException {
        ResultSetMetaData md = rs.getMetaData(); //得到结果集(rs)的结构信息，比如字段数、字段名等
        int columnCount = md.getColumnCount();
        while(rs.next())
        {
            StringBuffer sb = new StringBuffer();
            for (int i = 1; i <= columnCount; i++) {
                sb.append(rs.getObject(i)+" ");
            }
            //log.debug(sb);
        }
    }


    /**
     * 调用存储过错
     **/
    public  Map<String,String> callPro(String sql) throws SQLException, ClassNotFoundException {

        Map<String,String> result = new HashMap();
        try {
            cs=conn.prepareCall(sql);
            cs.registerOutParameter(1, java.sql.Types.VARCHAR);
            cs.registerOutParameter(2, java.sql.Types.VARCHAR);
            cs.registerOutParameter(3, java.sql.Types.VARCHAR);
            cs.registerOutParameter(4, java.sql.Types.VARCHAR);
            rs = cs.executeQuery();
            result.put("code",cs.getString(1));
            result.put("stateno",cs.getString(2));
            result.put("msg",cs.getString(3));
            result.put("step_name",cs.getString(4));
            /*
                while(rs.next()) {                  // 输出RETURN的值
                    System.out.println(rs.getString(1));
                }
            */
        }  finally {
                rs.close();
                cs.close();
        }
        return result;
    }


}
